--****************************************************************************
--Table: GAIOVIEN
--****************************************************************************

--select
if exists(select name from sysobjects where name='sp_Select_GiaoVien')
drop proc sp_Select_GiaoVien
go
create proc sp_Select_GiaoVien
as 
begin
	select * from GIAOVIEN
end
exec  sp_Select_GiaoVien

--insert
if exists(select name from sysobjects where name='sp_Insert_GiaoVien')
drop proc sp_Insert_GiaoVien
go
create proc sp_Insert_GiaoVien
	@MaGiaoVien varchar(7),
	@TenGiaoVien nvarchar(31),
    @ChucDanh nvarchar(20),
    @HocHam nvarchar(31),
    @HocVi nvarchar(31),
    @Email nvarchar(51),
    @DienThoai varchar(12),
    @HinhAnh nvarchar(100),
    @HuongNghienCuu nvarchar(100),
    @NoiCongTac nvarchar(51),
    @MaBoMon int
as 
begin
	declare @flag int
	set @flag=0

	if exists (select * from BOMON where MaBoMon=@MaBoMon)
		set @flag = 1

	begin tran
	if(exists(select * from GIAOVIEN where MaGiaoVien=@MaGiaoVien))
		begin
			raiserror (N'Thông tin cần thêm vào đã có trong CSDL!!!', 16,1)
			rollback tran
			return
		end

		else
		begin
			if (@flag =1)
			begin
				insert into GIAOVIEN values(@MaGiaoVien,@TenGiaoVien,@ChucDanh,@HocHam,@HocVi,@Email,@DienThoai,@HinhAnh,@HuongNghienCuu,@NoiCongTac,@MaBoMon)
				commit tran
			end
			else
			begin
				raiserror (N'Thông tin cần thêm không hợp lệ!!!',1,16)
				rollback tran
				return
			end				
		end
end

--delete
if exists(select name from sysobjects where name='sp_Delete_GiaoVien')
drop proc sp_Delete_GiaoVien
go
create proc sp_Delete_GiaoVien
	@MaGiaoVien varchar(7)
as 
begin
	begin tran
	if not exists(select* from GIAOVIEN where MaGiaoVien=@MaGiaoVien)
	begin
		raiserror (N'Thông tin cần xoá không tồn tại!!!',16,1)
		rollback tran
		return
	end
	else --if( exists (select * from GiaoVien where MaGiaoVien = @MaGiaoVien))
		begin 
			Delete from GIAOVIEN where MaGiaoVien = @MaGiaoVien
			commit tran			
		end 
end

--Update
if exists(select name from sysobjects where name='sp_Update_GiaoVien')
drop proc sp_Update_GiaoVien
go
create proc sp_Update_GiaoVien
	@MaGiaoVien varchar(7),
	@TenGiaoVien nvarchar(31),
    @ChucDanh nvarchar(20),
    @HocHam nvarchar(31),
    @HocVi nvarchar(31),
    @Email nvarchar(51),
    @DienThoai varchar(12),
    @HinhAnh nvarchar(100),
    @HuongNghienCuu nvarchar(100),
    @NoiCongTac nvarchar(51),
    @MaBoMon int
as 
begin
	declare @flag int
	set @flag=0

	if exists (select * from BOMON where MaBoMon=@MaBoMon)
		set @flag = 1
	
	begin tran
	if(@flag=1)
		begin
			Update GIAOVIEN 
			set TenGiaoVien=@TenGiaoVien,ChucDanh=@ChucDanh,HocHam=@HocHam,HocVi=@HocVi,Email=@Email,DienThoai=@DienThoai,HinhAnh=@HinhAnh,HuongNghienCuu=@HuongNghienCuu,NoiCongTac=@NoiCongTac,MaBoMon=@MaBoMon
			where MaGiaoVien = @MaGiaoVien
			commit tran	
		end 
	else --flag=0
		begin
			raiserror (N'Thông tin cần cập nhật không hợp lệ !!!',1,16)
			rollback tran
			return
		end		
end


